/*
 * Copyright (c) 2018, apexes.net. All rights reserved.
 *
 *         http://www.apexes.net
 *
 */
package net.apexes.demo.query;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.gson.Gson;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.sql.H2Templates;
import com.querydsl.sql.SQLBindings;
import com.querydsl.sql.SQLQuery;
import com.querydsl.sql.SQLQueryFactory;
import com.querydsl.sql.SQLTemplates;
import com.querydsl.sql.dml.SQLInsertClause;
import net.apexes.commons.lang.Enume;
import net.apexes.commons.lang.IDs;
import net.apexes.commons.querydsl.Querydsl;
import net.apexes.commons.querydsl.QuerydslDao;
import net.apexes.demo.codegen.entity.enums.PayType;
import net.apexes.demo.codegen.entity.enums.StatusFlag;
import net.apexes.demo.codegen.enume.support.DemoEnumeSupport;
import net.apexes.demo.codegen.querydsl.entity.Pay;
import net.apexes.demo.codegen.querydsl.entity.Template;
import net.apexes.demo.codegen.querydsl.qvar.QPay;
import net.apexes.demo.codegen.querydsl.qvar.QTemplate;
import net.apexes.demo.codegen.util.DemoDatabases;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @author <a href=mailto:hedyn@foxmail.com>HeDYn</a>
 */
public class QuerydslDemo {

    public static void main(String[] args) throws Exception {
        DataSource dataSource = DemoDatabases.createDataSource();
        SQLTemplates templates = H2Templates.builder().build();

        SQLQueryFactory factory = Querydsl.ofSimple(templates)
                .registerAll(DemoEnumeSupport.getInstance().getEnumeTypes())
                .createQueryFactory(dataSource);

        testSQL(factory);
        testInsert(factory);
        testSelect(factory);
    }

    static void testSQL(SQLQueryFactory factory) throws Exception {
        BooleanExpression expr = null;
        SQLQuery<?> q;

        expr = QPay.$.statusFlag.eq(Enume.fromValue(StatusFlag.class, "R"))
                .or(QPay.$.name.eq("aaa"))
                .and(QPay.$.defaultValue.isNull())
                .and(QPay.$.defaultValue.eq(BigDecimal.ONE))
                .or(QPay.$.name.eq("bbb"))
                .and(QPay.$.defaultValue.eq(BigDecimal.TEN).not());
        q = factory.selectOne().from(QPay.$).where(expr);
        System.out.println("------------");
        System.out.println(q.getSQL().getSQL());

        expr = QPay.$.statusFlag.eq(Enume.fromValue(StatusFlag.class, "R"))
                .or(QPay.$.name.eq("aaa"))
                .or(QPay.$.name.eq("zzz"))
                .and(QPay.$.defaultValue.eq(BigDecimal.ONE))
                .and(QPay.$.name.eq("bbb").and(QPay.$.defaultValue.isNull()).not())
                .and(QPay.$.defaultValue.eq(BigDecimal.TEN));
        q = factory.selectOne().from(QPay.$).where(expr);
        System.out.println("------------");
        System.out.println(q.getSQL().getSQL());

        expr = QPay.$.statusFlag.gt(Enume.fromValue(StatusFlag.class, "R"));
        q = factory.selectOne().from(QPay.$).where(expr);
        System.out.println("------------");
        System.out.println(q.getSQL().getSQL());
    }

    static void testSelect(SQLQueryFactory factory) throws Exception {
        ObjectMapper mapper = DemoEnumeSupport.getInstance().createObjectMapper();
        Gson gson = DemoEnumeSupport.getInstance().createGsonBuilder().create();

        SQLQuery<Pay> query = null;
        BooleanExpression expr = null;
        List<Pay> list;

        SQLQuery<?> q;


        expr = QPay.$.statusFlag.eq(Enume.fromValue(StatusFlag.class, "R"))
                .or(QPay.$.name.eq("aaa").not())
                .and(QPay.$.defaultValue.isNull().or(QPay.$.defaultValue.eq(BigDecimal.ONE)).not())
                .and(QPay.$.defaultValue.eq(BigDecimal.TEN).and(QPay.$.defaultValue.eq(BigDecimal.ZERO)))
                .or(QPay.$.name.eq("bbb"));
        q = factory.selectOne().from(QPay.$).where(expr);
        System.out.println(q.getSQL().getSQL());

        expr = QPay.$.statusFlag.eq(Enume.fromValue(StatusFlag.class, "R"));
        q = factory.selectOne().from(QPay.$).where(expr);
        System.out.println(q.getSQL().getSQL());
        System.out.println("R exists=" + (q.fetchFirst() != null)
                + ", count=" + factory.selectFrom(QPay.$).where(expr).fetchCount());

        expr = QPay.$.statusFlag.eq(StatusFlag.VALID);
        q = factory.selectOne().from(QPay.$).where(expr);
        System.out.println(q.getSQL().getSQL());
        System.out.println("V exists=" + (q.fetchFirst() != null)
                + ", count=" + factory.selectFrom(QPay.$).where(expr).fetchCount());


        query = factory.select(QPay.$).from(QPay.$).orderBy(QPay.$.payType.asc());
        System.out.println(query.getSQL().getSQL() + "; " + query.getSQL().getNullFriendlyBindings());
        list = query.fetch();
        System.out.println("1=============================" + list.size());
        print(mapper, gson, list);

        expr = QPay.$.statusFlag.eq(StatusFlag.VALID);
        query = factory.select(QPay.$).from(QPay.$).where(expr).orderBy(QPay.$.payType.asc());
        System.out.println(query.getSQL().getSQL() + "; " + query.getSQL().getNullFriendlyBindings());
        list = query.fetch();
        System.out.println("2=============================" + list.size());
        print(mapper, gson, list);

        QuerydslDao<Pay, String> dao = new QuerydslDao<>(factory, QPay.$);
        list = dao.find(QPay.$.statusFlag.eq(StatusFlag.VALID));
        System.out.println("3=============================" + list.size());
        print(mapper, gson, list);
    }

    private static void print(ObjectMapper mapper, Gson gson, List<Pay> list) throws Exception {
        for (Pay pay : list) {
            PayType type = pay.getPayType();
            System.out.println(" ---- " + type + "(" + (type == null ? "null" : type.value()) + ")");
            String json = mapper.writeValueAsString(pay);
            System.out.println("jackson: " + json);
            Pay temp = mapper.readValue(json, Pay.class);
            System.out.println("jackson: " + mapper.writeValueAsString(temp));
            json = gson.toJson(temp);
            System.out.println("gson: " + json);
            temp = gson.fromJson(json, Pay.class);
            System.out.println("gson: " + gson.toJson(temp));
        }
    }

    static void testInsert(com.querydsl.sql.SQLQueryFactory queryFactory) {
        SQLInsertClause insertClause;

//        queryFactory.delete(QPay.$).execute();

        queryFactory.insert(QTemplate.$).populate(createTemplate());

        insertClause = queryFactory.insert(QPay.$).populate(cratePayByCash(StatusFlag.VALID));
        for (SQLBindings sql : insertClause.getSQL()) {
            System.out.println(sql.getSQL());
        }
        System.out.println("insertExNull : " + insertClause.execute());

        insertClause = queryFactory.insert(QPay.$).populate(cratePayByCash(StatusFlag.INVALID));
        for (SQLBindings sql : insertClause.getSQL()) {
            System.out.println(sql.getSQL());
        }
        System.out.println("insertExNull : " + insertClause.execute());

        insertClause = queryFactory.insert(QPay.$).populate(cratePayByUnknown(StatusFlag.VALID, "X"));
        for (SQLBindings sql : insertClause.getSQL()) {
            System.out.println(sql.getSQL());
        }
        System.out.println("insertExNull : " + insertClause.execute());

        insertClause = queryFactory.insert(QPay.$).populate(cratePayByUnknown(StatusFlag.VALID, "Y"));
        for (SQLBindings sql : insertClause.getSQL()) {
            System.out.println(sql.getSQL());
        }
        System.out.println("insertExNull : " + insertClause.execute());

        insertClause = queryFactory.insert(QPay.$).populate(cratePayByUnknown(StatusFlag.INVALID, "Y"));
        for (SQLBindings sql : insertClause.getSQL()) {
            System.out.println(sql.getSQL());
        }
        System.out.println("insertExNull : " + insertClause.execute());

        insertClause = queryFactory.insert(QPay.$).populate(cratePayByUnknown(StatusFlag.VALID, "Z"));
        for (SQLBindings sql : insertClause.getSQL()) {
            System.out.println(sql.getSQL());
        }
        System.out.println("insertExNull : " + insertClause.execute());
    }

    static Pay cratePayByCash(StatusFlag statusFlag) {
        return createPay(statusFlag, PayType.CASH);
    }

    static Pay cratePayByUnknown(StatusFlag statusFlag, String value) {
        PayType payType = Enume.fromValue(PayType.class, value);
        return createPay(statusFlag, payType);
    }

    static Pay createPay(StatusFlag statusFlag, PayType payType) {
        Pay pay = new Pay();
        pay.setId(IDs.newId());
        pay.setAllowChange(true);
        pay.setAllowFavoured(true);
        pay.setCrtTime(LocalDateTime.now());
        pay.setCrtWho("admin");
        pay.setName("现金");
        pay.setPayType(payType);
        pay.setDefaultValue(null);
        pay.setStatusFlag(statusFlag);
        pay.setVersion("000");
        pay.setTemplateId(TEMPLATE_ID);
        return pay;
    }

    static final String TEMPLATE_ID = "2eKcArWSuB5woh2Kv";

    private static Template createTemplate() {
        Template template = new Template();
        template.setId(TEMPLATE_ID);
        template.setName("默认");
        template.setStatusFlag(StatusFlag.VALID);
        template.setVersion(TEMPLATE_ID);
        return template;
    }

}
